package com.example.sqlcrypt;

import android.content.Context;
import android.util.Log;
import net.sqlcipher.Cursor;
import net.sqlcipher.database.SQLiteDatabase;

import java.util.ArrayList;
import java.util.List;

/**
 * description: TODO
 *
 * @author: wzq
 */
public class DaoStu {
    private static final String DB_PASSWORD = "dbPassword";

    /**
     *
     */
    private DaoStu() {
        super();
    }

    /**
     * 查询CCB_HOME_MENU 表是否存在
     */
    public static boolean doesTableExists(Context ctx) {
        DbHelper helper = new DbHelper(ctx);
        SQLiteDatabase db = helper.getWritableDatabase(DB_PASSWORD);
        String sql = "select DISTINCT tbl_name from sqlite_master where tbl_name = 'stu'";
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql, null);
            if(cursor != null) {
                if(cursor.getCount() > 0) {
                    return true;
                }
            }
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(cursor != null && !cursor.isClosed()) { cursor.close(); }
                db.close();
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    /**
     * @param ctx Context
     */
    public static void dropTable(Context ctx) {
        String sqlDrop = "drop table if exists stu";
        DbHelper helper = new DbHelper(ctx);
        SQLiteDatabase db = helper.getWritableDatabase(DB_PASSWORD);
        db.execSQL(sqlDrop);
        db.close();
    }

    /**
     * @param ctx
     */
    public static void clearTable(Context ctx) {
        String sqlClear = "delete from stu";
        DbHelper helper = new DbHelper(ctx);
        SQLiteDatabase db = helper.getWritableDatabase(DB_PASSWORD);
        db.execSQL(sqlClear);
        db.close();
    }

    /**
     * @param ctx
     */
    public static void createTableIfNotExists(Context ctx) {
        String sqlCreate = "create table if not exists stu(" + " id integer primary key autoincrement," + " name varchar(30)," + " addr varchar(30)," + " age integer)";
        DbHelper helper = new DbHelper(ctx);
        SQLiteDatabase db = helper.getWritableDatabase(DB_PASSWORD);
        db.execSQL(sqlCreate);
        db.close();
    }

    /**
     * @param ctx
     * @param aStu
     */
    public static void insert(Context ctx, BeanStu aStu) {
        String insert = "insert into stu(name,addr,age) values(?,?,?)";
        String name = aStu.getName();
        String addr = aStu.getAddr();
        int age = aStu.getAge();

        DbHelper helper = new DbHelper(ctx);
        long before = System.currentTimeMillis();
        SQLiteDatabase db = helper.getWritableDatabase(DB_PASSWORD);
        System.out.println("insert (ms) = " + (System.currentTimeMillis() - before));
        db.execSQL(insert, new Object[]{name, addr, age});
        db.close();

    }


    public static List<BeanStu> queryBySql(Context ctx, String sql) {
        DbHelper helper = new DbHelper(ctx);

        //
        long before = System.currentTimeMillis();
        SQLiteDatabase db = helper.getWritableDatabase(DB_PASSWORD);
        System.out.println("query (ms) = " + (System.currentTimeMillis() - before));

        Cursor cursor = db.rawQuery(sql, null);
        ArrayList<BeanStu> stuList = new ArrayList<BeanStu>();
        if(cursor.moveToFirst()) {
            while(!cursor.isAfterLast()) {
                String name = cursor.getString(cursor.getColumnIndex("name"));
                String addr = cursor.getString(cursor.getColumnIndex("addr"));
                int age = cursor.getInt(cursor.getColumnIndex("age"));
                stuList.add(new BeanStu(name, addr, age));
                //
                cursor.moveToNext();
            }
        }
        // 释放
        if(cursor != null) {
            cursor.close();
        }
        db.close();

        //
        return stuList;
    }

    public static List<BeanStu> queryByAddr(Context ctx, String addrInfo) {
        String sqlQueryByAddr = "select * from stu where addr='%s'";
        String sql = String.format(sqlQueryByAddr, addrInfo);
        return queryBySql(ctx, sql);
    }

    public static List<BeanStu> queryAll(Context ctx) {
        String sql = "select * from stu ";
        return queryBySql(ctx, sql);
    }
}
